<?php
## from P_Cast.php
function PDESGetCastRolePathList() {
	$dbh = DB3::get();
	$sql = 'SELECT id, path FROM cast_role ORDER BY id';

	$ps = $dbh->prepare($sql);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$role_list = array();
	while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$role_list[$row['id']] = $row['path'];
	}
	
	return $role_list;
}

function PDESGetCastById($castId) {
	$dbh = DB3::get();
	$sql = '
		SELECT c.*,
		lc.real_first_name AS real_first_name_chinese,
		lc.real_last_name AS real_last_name_chinese,
		lc.first_name AS first_name_chinese,
		lc.last_name AS last_name_chinese,
		lc.other_name AS other_name_chinese,
		lc.nickname AS nickname_chinese,
		lc.occupation AS occupation_chinese,
		lc.personality AS personality_chinese,
		lc.profile AS profile_chinese,
		lc.history AS history_chinese,
		le.real_first_name AS real_first_name_english,
		le.real_last_name AS real_last_name_english,
		le.first_name AS first_name_english,
		le.last_name AS last_name_english,
		le.other_name AS other_name_english,
		le.nickname AS nickname_english,
		le.occupation AS occupation_english,
		le.personality AS personality_english,
		le.profile AS profile_english,
		le.history AS history_english
		FROM p_cast c
			LEFT JOIN `p_cast-language` lc ON c.id=lc.cast_id AND lc.language_id=:langChnId
			LEFT JOIN `p_cast-language` le ON c.id=le.cast_id AND le.language_id=:langEngId
		WHERE c.id=:id AND c.status=1
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$castId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($cast = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $cast;
}

function PDESGetCastListByProgrammeId($programmeId) {
	$dbh = DB3::get();
	$sql = '
		SELECT c.*, 
		lc.real_first_name AS real_first_name_chinese,
		lc.real_last_name AS real_last_name_chinese,
		lc.first_name AS first_name_chinese,
		lc.last_name AS last_name_chinese,
		lc.other_name AS other_name_chinese,
		lc.nickname AS nickname_chinese,
		lc.occupation AS occupation_chinese,
		lc.personality AS personality_chinese,
		lc.profile AS profile_chinese,
		lc.history AS history_chinese,
		le.real_first_name AS real_first_name_english,
		le.real_last_name AS real_last_name_english,
		le.first_name AS first_name_english,
		le.last_name AS last_name_english,
		le.other_name AS other_name_english,
		le.nickname AS nickname_english,
		le.occupation AS occupation_english,
		le.personality AS personality_english,
		le.profile AS profile_english,
		le.history AS history_english
		FROM p_cast c
			LEFT JOIN `p_cast-language` lc ON c.id=lc.cast_id AND lc.language_id=:langChnId
			LEFT JOIN `p_cast-language` le ON c.id=le.cast_id AND le.language_id=:langEngId
		WHERE c.programme_id=:id AND c.status=1
	';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$programmeId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$casts = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$casts[$row['id']] = $row;
	}
	return $casts;
}

function PDESGetCastRoles() {
	$dbh = DB3::get();
	$sql = 'SELECT * FROM cast_role';
	$ps = $dbh->prepare($sql);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$casts = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$casts[$row['id']] = $row;
	}
	return $casts;
}

function PDESGetCastDictionaryById($id) {
	$dbh = DB3::get();
	
	$sql = 'SELECT * FROM pdes.cast_dictionary WHERE id = :id';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id', $id);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}
	
	if ( !($cast = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $cast;
}
function PDESGetCastIiId($folders){
	$result = array('head_image'=>array(),'full_body_image'=>array());
	$folders = trim($folders);
	if($folders){
		$folder_type = array('head_image'=>14,'full_body_image'=>15);
		$dbh = DB3::get();
		$sql = "
			SELECT DISTINCT i.ii_id,f.folder_type_id FROM p_image i 
			INNER JOIN p_folder_item fi ON (i.id=fi.item_id) AND (fi.folder_id IN($folders))
			INNER JOIN p_folder f ON (fi.folder_id=f.id) AND (f.folder_item_type_id=". AC::FOLDER_TYPE_IMAGE .") 
				AND (f.folder_type_id={$folder_type['head_image']} OR f.folder_type_id={$folder_type['full_body_image']})
			ORDER BY f.id DESC,fi.rank ASC
		";
		$ps = $dbh->prepare($sql);
		$ret = $ps->execute();
		if(!$ret) {
			error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
			return false;
		}
		while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
			if($folder_type['head_image'] == $row['folder_type_id'])
			{
				$result['head_image'][] = $row['ii_id'];
			}
			elseif($folder_type['full_body_image'] == $row['folder_type_id'])
			{
				$result['full_body_image'][] = $row['ii_id'];
			}
		}
	}
	return $result;
}

## from P_Category.php
function PDESGetCategoryById($catId) {
	$dbh = DB3::get();
	$sql = '
		SELECT c.*,l.* FROM tvbcom_category c
			LEFT JOIN `tvbcom_category-language` l ON c.id=l.tvbcom_category_id AND l.language_id=1
		WHERE c.id=:id
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$catId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($cat = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $cat;
}

## from P_Chapter.php
function PDESGetChapterById($chapterId) {
	$dbh = DB3::get();
	$sql = '
		SELECT c.*,
		lc.title AS title_chinese,
		lc.description AS description_chinese,
		le.title AS title_english,
		le.description AS description_english
		FROM p_chapter c
			LEFT JOIN `p_chapter-language` lc ON c.id=lc.chapter_id AND lc.language_id=:langChnId
			LEFT JOIN `p_chapter-language` le ON c.id=le.chapter_id AND le.language_id=:langEngId
		WHERE c.id=:id AND c.status = 1
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$chapterId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($chapter = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $chapter;
}

function PDESGetChapterListByVideoId($videoId) {
	$dbh = DB3::get();
	$sql = 'SELECT c.id FROM p_chapter c WHERE c.video_id=:id AND c.status=1';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$videoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$chapters = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$chapters[$row['id']] = $row;
	}
	return $chapters;
}

function PDESGetChapterCategoriesByChapterId($chapterId) {
	$dbh = DB3::get();

	$sql = 'SELECT c.* FROM `p_chapter-tvbcom_category` c WHERE c.chapter_id=:id ORDER BY rank';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id', $chapterId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$categories = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$categories[$row['category_id']] = $row;
	}

	return $categories;
}

## from P_Episode.php
function PDESGetEpisodeById($episodeId) {
	$dbh = DB3::get();
	$sql = '
		SELECT e.*,
		lc.title AS title_chinese,
		lc.description AS description_chinese,
		lc.description_short AS description_short_chinese,
		lc.editor_message AS editor_message_chinese,
		le.title AS title_english,
		le.description AS description_english,
		le.description_short AS description_short_english,
		le.editor_message AS editor_message_english
		FROM p_episode e
			LEFT JOIN `p_episode-language` lc ON e.id=lc.episode_id AND lc.language_id=:langChnId
			LEFT JOIN `p_episode-language` le ON e.id=le.episode_id AND le.language_id=:langEngId
		WHERE e.id=:id AND e.status = 1
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$episodeId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($episode = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $episode;
}

function PDESGetEpisodeListByProgrammeId($programmeId) {
	$dbh = DB3::get();
	$sql = '
		SELECT e.*,
		lc.title AS title_chinese,
		lc.description AS description_chinese,
		lc.description_short AS description_short_chinese,
		lc.editor_message AS editor_message_chinese,
		le.title AS title_english,
		le.description AS description_english,
		le.description_short AS description_short_english,
		le.editor_message AS editor_message_english
		FROM p_episode e
			LEFT JOIN `p_episode-language` lc ON e.id=lc.episode_id AND lc.language_id=:langChnId
			LEFT JOIN `p_episode-language` le ON e.id=le.episode_id AND le.language_id=:langEngId
		WHERE e.programme_id=:id AND e.status=1
	';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$programmeId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$episodes = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$episodes[$row['id']] = $row;
	}
	return $episodes;
}

function PDESGetEpisodicCatchupListByEpisodeId($episodeId) {
	$dbh = DB3::get();
	$sql = '
		SELECT v.* FROM p_folder_item i
			JOIN p_video v ON i.item_id=v.id
			JOIN p_folder f ON i.folder_id=f.id AND folder_item_type_id=1
			JOIN `p_episode-video` ev ON i.item_id=ev.video_id
		WHERE ev.episode_id=:id AND v.status=1
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$programmeId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$catchups = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$catchups[$row['id']] = $row;
	}

	return $catchups;
}


## from P_Folder.php
function PDESGetAllFolderListByFolderIds($folderIds) {
	if(!is_array($folderIds)) {
		$folderIds = explode(',',$folderIds);
	}

	if(count($folderIds) == 0) {
		return array();
	}

	$dbh = DB3::get();
	$folders = array();

	foreach($folderIds as $id) {
		$sql = '
			SELECT f.*,
			lc.title AS title_chinese,
			lc.description AS description_chinese,
			le.title AS title_english,
			le.description AS description_english
			FROM p_folder f
				LEFT JOIN `p_folder-language` lc ON f.id=lc.folder_id AND lc.language_id=:langChnId
				LEFT JOIN `p_folder-language` le ON f.id=le.folder_id AND le.language_id=:langEngId
			WHERE f.id = :folderId AND f.status=1
		';
		$ps = $dbh->prepare($sql);
		$ps->bindValue('folderId',$id);
		$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
		$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
		$ret = $ps->execute();

		if(!$ret) {
			error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
			return false;
		}

		$i = 0;
		if($row = $ps->fetch(PDO::FETCH_ASSOC)) {
			$folders[$row['id']] = $row;
			$i++;
		}
	}
	return $folders;
}
function PDESGetFolderItemListByFolderId($folderId) {

	if(!$folderId) {
		return false;
	}

	$dbh = DB3::get();

	$sql = '
		SELECT f.*,
		lc.title AS title_chinese,
		lc.description AS description_chinese,
		le.title AS title_english,
		le.description AS description_english
		FROM p_folder_item f
			LEFT JOIN `p_folder_item-language` lc ON f.folder_id = lc.folder_id AND f.item_id=lc.item_id AND lc.language_id=:langChnId
			LEFT JOIN `p_folder_item-language` le ON f.folder_id = le.folder_id AND f.item_id=le.item_id AND le.language_id=:langEngId	
		WHERE f.folder_id=:folder_id order by f.rank;
	';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('folder_id',$folderId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$items = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$items[$row['item_id']] = $row;
	}
	return $items;
}

## from P_Image.php
function PDESGetImageById($imageId) {
	$dbh = DB3::get();
	$sql = '
		SELECT i.*,
		IFNULL(fc.watermark_id,0) AS watermark_id ,
		lc.title AS title_chinese,
		lc.description AS description_chinese,
		le.title AS title_english,
		le.description AS description_english
		FROM p_image i
			INNER JOIN `p_folder_item` fi ON i.id = fi.item_id 
			INNER JOIN `p_folder` f ON fi.folder_id = f.id AND f.folder_item_type_id = '. AC::FOLDER_TYPE_IMAGE .'
			LEFT JOIN `p_image_folder-config` fc ON f.id = fc.folder_id
			LEFT JOIN `p_folder_item-language` lc ON fi.folder_id = lc.folder_id AND fi.item_id=lc.item_id AND lc.language_id=:langChnId
			LEFT JOIN `p_folder_item-language` le ON fi.folder_id = le.folder_id AND fi.item_id=le.item_id AND le.language_id=:langEngId	
		WHERE i.id=:id AND i.status = 1
		LIMIT 1
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$imageId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($image = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $image;
}

function PDESGetImageProfiles($type) {

	$file = '/data/pms_db3/conf/image_profiles.xml';

	$xml = simplexml_load_file($file);
	$node = $xml->xpath("/profiles/profile[@type='{$type}']");
	$profileList = array();

	if(is_array($node)) {
		foreach ($node as $profile) {

			$format = (string) $profile->attributes()->name;
			$type = (string) $profile->attributes()->type;
			$id = (int) $profile->attributes()->id;
			$width = (int) $profile->width;
			$height = (int) $profile->height;
			$mode = (string) $profile->mode;
			$watermark = (string) $profile->watermark;
			$product = (string) $profile->product;

			$profileList[$id] = array(
				'format' => $format,
				'width' => $width,
				'height' => $height,
				'mode' => $mode,
				'product' => $product,
				'watermark' => $watermark ? $watermark : '',
				'type' => $type,
			);

		}
	}

	return $profileList;

}


## from P_Info.php
function PDESGetInfoListByEpisodeId($episodeId) {
	$dbh = DB3::get();

	$infoTypes = array(
		1=>'address',
		'award',
		'biography',
		'general_information',
		'product',
		'q_a',
		'recipe',
		'song',
	);

	$infos = array();

	foreach($infoTypes as $type) {
		$sql = "SELECT * FROM p_info_{$type} WHERE episode_id=:id AND status = 1";
		$ps = $dbh->prepare($sql);
		$ps->bindValue('id',$episodeId);
		$ret = $ps->execute();

		if(!$ret) {
			error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
			return false;
		}

		if($type == 'general_information') {
			$type = 'general_info';
		}
		elseif($type == 'q_a') {
			$type = 'qa';
		}
		while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
			$infos[$type][$row['id']] = $row;
		}
	}

	return $infos;
}
function PDESGetInfoAddressById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_address i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoAwardById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_award i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoBiographyById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_biography i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoGeneralById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_general_information i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoProductById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_product i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoQAById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_q_a i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoRecipeById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_recipe i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

function PDESGetInfoSongById($infoId) {
	$dbh = DB3::get();
	$sql = 'SELECT i.* AS chapter_id FROM p_info_song i WHERE i.id=:id AND i.status = 1';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$infoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($info = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $info;
}

## from P_Misc.php
function PDESGetCountryList() {
	$dbh = DB3::get();
	$sql = 'SELECT * FROM country';
	$ps = $dbh->prepare($sql);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$countries = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$countries[$row['id']] = $row;
	}
	return $countries;

}

function PDESGetProvinceList() {
	$dbh = DB3::get();
	$sql = 'SELECT * FROM province_state_prefecture';
	$ps = $dbh->prepare($sql);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$province = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$province[$row['id']] = $row;
	}
	return $province;

}

## from P_Tag.php
function PDESGetTagById($tagId) {
	$dbh = DB3::get();

	$sql = "SELECT * FROM p_tag WHERE id=:id";

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$tagId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($tag = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}
	return $tag;
}

## from P_Video.php
function PDESGetVideoById($videoId) {
	$dbh = DB3::get();
	$sql = '
		SELECT v.*,
			e.episode_id,
			lc.title AS title_chinese,
			lc.description AS description_chinese,
			le.title AS title_english,
			le.description AS description_english,
			v.vi_id AS source_id,
			pre.title_zh AS premium_title_chinese,
			pre.title_en AS premium_title_english,
			vlcn.subtitle AS subtitle_cn,
			vlen.subtitle AS subtitle_en 
		FROM p_video v
			INNER JOIN `p_folder_item` fi ON v.id = fi.item_id 
			INNER JOIN `p_folder` f ON fi.folder_id = f.id AND f.folder_item_type_id = 1	
			LEFT JOIN `p_programme_premium`      pre ON v.programme_id=pre.programme_id 
			LEFT JOIN `p_episode-video` e ON v.id=e.video_id	
			LEFT JOIN `p_folder_item-language` lc ON fi.folder_id = lc.folder_id AND fi.item_id=lc.item_id AND lc.language_id=:langChnId
			LEFT JOIN `p_folder_item-language` le ON fi.folder_id = le.folder_id AND fi.item_id=le.item_id AND le.language_id=:langEngId	
			LEFT JOIN `p_video-language` vlcn ON v.id = vlcn.video_id AND vlcn.language_id = :langChnId
			LEFT JOIN `p_video-language` vlen ON v.id = vlen.video_id AND vlen.language_id = :langEngId
		WHERE v.id=:id AND v.status = 1
		LIMIT 1
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$videoId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($video = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}

	$videoId = (int)$videoId;
	$premium_sql = "SELECT COUNT(video_id) FROM p_video_premium WHERE video_id=$videoId AND (ppv_flag=1 OR subscription_flag=1)";
	$res = $dbh->query($premium_sql);
	if($res->fetchColumn()>0){ // Video
		$premium_info = "
			SELECT 
			  p.ppv_flag, p.subscription_flag,
			  pv.country_allow_list AS ppv_country_allow_list,
			  pv.country_deny_list  AS ppv_country_deny_list,
			  pv.publish_datetime   AS ppv_online_time,
			  pv.expire_datetime    AS ppv_offline_time,
			  ps.country_allow_list AS subscription_country_allow_list,
			  ps.country_deny_list  AS subscription_country_deny_list,
			  ps.publish_datetime   AS subscription_online_time,
			  ps.expire_datetime    AS subscription_offline_time,
			  ps.free_flag          AS subscription_free_flag
			FROM p_video_premium p
			LEFT JOIN p_video_ppv pv ON p.video_id=pv.video_id
			LEFT JOIN p_video_subscription ps ON p.video_id=ps.video_id
			WHERE p.video_id=$videoId
		";
	}else{ // Programme
		$premium_info = "
			SELECT 
			  p.ppv_flag, p.subscription_flag,
			  pv.country_allow_list AS ppv_country_allow_list,
			  pv.country_deny_list  AS ppv_country_deny_list,
			  pv.publish_datetime   AS ppv_online_time,
			  pv.expire_datetime    AS ppv_offline_time,
			  ps.country_allow_list AS subscription_country_allow_list,
			  ps.country_deny_list  AS subscription_country_deny_list,
			  ps.publish_datetime   AS subscription_online_time,
			  ps.expire_datetime    AS subscription_offline_time,
			  ps.free_flag          AS subscription_free_flag
			FROM p_programme_premium p
			LEFT JOIN p_programme_ppv pv ON p.programme_id=pv.programme_id
			LEFT JOIN p_programme_subscription ps ON p.programme_id=ps.programme_id
			WHERE p.programme_id = (SELECT programme_id FROM p_video WHERE id=$videoId)
		";
	}
	$premium_data = $dbh->query($premium_info)->fetch(PDO::FETCH_ASSOC);
	if($premium_data) $video = array_merge($video, $premium_data);

	return $video;
}

function PDESGetVideoExportByVideoId($videoId) {
	$dbh = DB3::get();
	$sql = 'SELECT ve.export_id,ve.export_params FROM p_video_export ve JOIN p_video v ON ve.video_id=v.id WHERE v.id=:id';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$videoId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$exports = array();
	while($row = $ps->fetch()) {
		$exports[$row['export_id']] = $row['export_params'];
	}
	return $exports;
}


function PDESGetVideoListByProgrammeId($programmeId) {
	$dbh = DB3::get();
	$sql = '
		SELECT v.*,
		e.episode_id,
		lc.title AS title_chinese,
		lc.description AS description_chinese,
		le.title AS title_english,
		le.description AS description_english
		FROM p_video v
			INNER JOIN `p_folder_item` fi ON v.id = fi.item_id 
			INNER JOIN `p_folder` f ON fi.folder_id = f.id AND f.folder_item_type_id = 1	
			LEFT JOIN `p_episode-video` e ON v.id=e.video_id	
			LEFT JOIN `p_folder_item-language` lc ON fi.folder_id = lc.folder_id AND fi.item_id=lc.item_id AND lc.language_id=:langChnId
			LEFT JOIN `p_folder_item-language` le ON fi.folder_id = le.folder_id AND fi.item_id=le.item_id AND le.language_id=:langEngId
		WHERE v.programme_id=:id AND v.status=1
	';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$programmeId);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$videos = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$videos[$row['id']] = $row;
	}
	return $videos;
}

function PDESGetVideoExportByExportId($exportId) {
	$dbh = DB3::get();
	$sql = 'SELECT ve.export_id,ve.export_params,ve.video_id FROM p_video_export ve WHERE ve.export_id=:id';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$exportId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$exports = array();
	while($row = $ps->fetch()) {
		$exports[$row['export_id']] = $row;
	}
	return $exports;
}


## from P_Programme.php
function PDESGetProgrammeById($id) {
	$dbh = DB3::get();

	$sql = '
		SELECT p.*,
			plc.title AS title_chinese,
			plc.settop_box_name AS settop_box_name_chinese,
			plc.description AS description_chinese,
			plc.description_short AS description_short_chinese,
			plc.fact_background AS fact_background_chinese,
			plc.objective AS objective_chinese,
			plc.first_onair_information AS first_onair_information_chinese,
			ple.title AS title_english,
			ple.settop_box_name AS settop_box_name_english,
			ple.description AS description_english,
			ple.description_short AS description_short_english,
			ple.fact_background AS fact_background_english,
			ple.objective AS objective_english,
			ple.first_onair_information AS first_onair_information_english,
			cpl.*,
			IF(c.parent_id>0,c.parent_id,c.id) AS primary_category_id
			/***** append by vanni for premium information. BEGIN *****/ 
			,pre.ppv_flag
			,pre.subscription_flag
			,pre.title_zh AS premium_title_chinese
			,pre.title_en AS premium_title_english
			,ppv.country_allow_list AS ppv_country_allow_list
			,ppv.country_deny_list AS ppv_country_deny_list
			,ppv.publish_datetime AS ppv_online_time
			,ppv.expire_datetime AS ppv_offline_time
			,pss.country_allow_list AS subscription_country_allow_list
			,pss.country_deny_list AS subscription_country_deny_list
			,pss.publish_datetime AS subscription_online_time
			,pss.expire_datetime AS subscription_offline_time
			,pss.free_flag AS subscription_free_flag
			/***** END *****/
		FROM p_programme p
			/***** append by vanni for premium information. BEGIN *****/ 
			LEFT JOIN `p_programme_subscription` pss ON p.id=pss.programme_id 
			LEFT JOIN `p_programme_ppv`          ppv ON p.id=ppv.programme_id 
			LEFT JOIN `p_programme_premium`      pre ON p.id=pre.programme_id 
			/***** END *****/
			LEFT JOIN `p_programme-language` plc ON p.id=plc.programme_id AND plc.language_id=:langChnId
			LEFT JOIN `p_programme-language` ple ON p.id=ple.programme_id AND ple.language_id=:langEngId
			LEFT JOIN p_programme_cpl cpl ON p.id=cpl.programme_id AND effective_date <= NOW()
			LEFT JOIN `p_programme-tvbcom_category` cat ON p.id=cat.programme_id
			JOIN tvbcom_category c ON cat.category_id=c.id
		WHERE p.id = :id AND p.status = 1
			AND (cpl.programme_id IS NOT NULL
					AND effective_date=(SELECT effective_date FROM p_programme_cpl
										WHERE programme_id=p.id ORDER BY effective_date DESC LIMIT 1)
					OR cpl.programme_id IS NULL)
		ORDER BY cat.rank, cpl.effective_date DESC
		LIMIT 1
	';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$id);
	$ps->bindValue('langChnId',PMSDB3Constants::LANG_CHT);
	$ps->bindValue('langEngId',PMSDB3Constants::LANG_ENG);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	if ( !($programme = $ps->fetch(PDO::FETCH_ASSOC)) ) {
		return FALSE;
	}

	return $programme;
}

function PDESGetProgrammeCategoriesByProgrammeId($programmeId) {
	$dbh = DB3::get();

	$sql = '
		SELECT pc.rank,c.*,l.*, c.path AS category_path
			FROM `p_programme-tvbcom_category` pc JOIN tvbcom_category c ON pc.category_id=c.id
			LEFT JOIN `tvbcom_category-language` l ON c.id=l.tvbcom_category_id AND l.language_id=1
		WHERE pc.programme_id=:id
	';
	$ps = $dbh->prepare($sql);
	$ps->bindValue('id',$programmeId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$categories = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$categories[$row['id']] = $row;
	}

	return $categories;
}

function PDESGetProgrammeChannelsByProgrammeId($programmeId) {
	$dbh = DB3::get();

	$sql = '
		SELECT p.id AS programme_id, p.first_network_codes, IF(c.parent_id > 0, c.parent_id, c.id) AS main_category_id, p.first_episode_time_onair, p.last_episode_time_onair
		FROM p_programme p
			INNER JOIN `p_programme-tvbcom_category` pc ON p.id = pc.programme_id
			INNER JOIN tvbcom_category c ON pc.category_id = c.id
		WHERE p.id = :id
	';

	$ps = $dbh->prepare($sql);
	$ps->bindValue('id', $programmeId);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$channels = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$tmp = explode(",", $row['first_network_codes']);
		unset($row['first_network_codes']);
		foreach ($tmp as $network_code) {
			$channel = $row;
			$channel['network_code'] = $network_code;
			$channels[] = $channel;
		}
	}

	return $channels;
}

function PDESGetAllProgrammeChannelsByProgrammeId() {
	$dbh = DB3::get();

	$sql = '
		SELECT p.id, p.first_network_codes, IF(c.parent_id > 0, c.parent_id, c.id) AS main_category_id, p.first_episode_time_onair, p.last_episode_time_onair
		FROM p_programme p
			INNER JOIN `p_programme-tvbcom_category` pc ON p.id = pc.programme_id
			INNER JOIN tvbcom_category c ON pc.category_id = c.id
	';

	$ps = $dbh->prepare($sql);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$channels = array();
	while($row = $ps->fetch(PDO::FETCH_ASSOC)) {
		$tmp = explode(",", $row['first_network_codes']);
		unset($row['first_network_codes']);
		foreach ($tmp as $network_code) {
			$channel = $row;
			$channel['network_code'] = $network_code;
			$channels[] = $channel;
		}
	}

	return $channels;
}

function PDESGetProgrammeIiId($folders){
	$folders = $folders ? $folders : '0';
	$folder_type = array('banner_image'=>16,'key_art_image'=>17);

	$dbh = DB3::get();
	$sql = "
		SELECT DISTINCT i.ii_id,f.folder_type_id FROM p_image i 
		INNER JOIN p_folder_item fi ON (i.id=fi.item_id) AND (fi.folder_id IN($folders))
		INNER JOIN p_folder f ON (fi.folder_id=f.id) AND (f.folder_item_type_id=". AC::FOLDER_TYPE_IMAGE .") 
			AND (f.folder_type_id={$folder_type['banner_image']} OR f.folder_type_id={$folder_type['key_art_image']})
		ORDER BY f.id DESC,fi.rank ASC
	";
	$ps  = $dbh->prepare($sql);
	$ret = $ps->execute();
	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$result = array('banner_image'=>array(),'key_art_image'=>array());
	while($row = $ps->fetch(PDO::FETCH_ASSOC)){
		if($row['folder_type_id'] == $folder_type['banner_image'])
		{
			$result['banner_image'][] = $row['ii_id'];
		}
		else if($row['folder_type_id'] == $folder_type['key_art_image'])
		{
			$result['key_art_image'][] = $row['ii_id'];
		}
	}
	return $result;
}


#### for new proofread flow 
function PDESGetFolderData($folder_id){
	$dbh = DB3::get();
	$sql = '
		SELECT f.*,
		fc.title AS title_chinese,
		fe.title AS title_english
		FROM p_folder f
			LEFT JOIN `p_folder-language` fc ON f.id = fc.folder_id AND fc.language_id=?
			LEFT JOIN `p_folder-language` fe ON f.id = fe.folder_id AND fe.language_id=?
		WHERE f.id=?
	';

	$ps  = $dbh->prepare($sql);
	$ret = $ps->execute(array(PMSDB3Constants::LANG_CHT,PMSDB3Constants::LANG_ENG,$folder_id));
	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}
	$item = $ps->fetch();

	# for bug 7281
	if(1 == $item['folder_item_type_id']){
		$sql = 'select m.id from p_folder_item fi,p_video m where fi.item_id=m.id and fi.folder_id=? and m.status=? limit 1';
	}else{
		$sql = 'select m.id from p_folder_item fi,p_image m where fi.item_id=m.id and fi.folder_id=? and m.status=? limit 1';
	}
	$ps = $dbh->prepare($sql);
	$ret = $ps->execute(array($folder_id,AC::STATUS_NEW));
	if(!$ps->fetch()){ // folder is empty
		return false;
	}

	$name = $item['title_chinese'] ? $item['title_chinese'] : $item['title_english'];
	$getter = new Processor;
	return array(
		'id' => $item['id'],
		'type' => $getter->folderTypes[$item['folder_type_id']],
		'name' => $name,
		'title' => $name,
		'title_chinese' => $item['title_chinese'],
		'title_english' => $item['title_english'],
		'contains' => ($item['folder_item_type_id'] == 1) ? 'video' : 'image'				
	);
}
function PDESGetFolderItemData($folder_id,$item_id){
	$dbh = DB3::get();
	$sql = '
		SELECT fi.*,
			lc.title AS title_chinese,
			lc.description AS description_chinese,
			le.title AS title_english,
			le.description AS description_english
		FROM p_folder_item fi 
			LEFT JOIN `p_folder_item-language` lc ON fi.folder_id = lc.folder_id AND fi.item_id=lc.item_id AND lc.language_id=?
			LEFT JOIN `p_folder_item-language` le ON fi.folder_id = le.folder_id AND fi.item_id=le.item_id AND le.language_id=?	
		WHERE fi.folder_id=? AND fi.item_id=?
	';
	$ps  = $dbh->prepare($sql);
	$ret = $ps->execute(array(PMSDB3Constants::LANG_CHT,PMSDB3Constants::LANG_ENG,$folder_id,$item_id));
	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	$row    = $ps->fetch();
	$rank   = $row['rank'];
	$getter = new Processor;

	$folder_data = PDESGetAllFolderListByFolderIds($folder_id);
	$contains    = $folder_data[$folder_id]['folder_item_type_id'] == 1 ? 'video' : 'image';
	$itemTypes   = $getter->getFolderItemTypes();
	$itemType    = $itemTypes[$contains];

	$item_type_data = CommonUtils::exec_sql($dbh,'select * from p_folder where id=?',array($folder_id));
	if($item_type_data[0]['folder_item_type_id']==AC::FOLDER_TYPE_IMAGE){
		$itemObj = PDESGetImageById($item_id);
	}else{
		$itemObj = PDESGetVideoById($item_id);
	}

	$folder_data = array(
		'folder_id'            => $folder_id,
		'folder_name'          => $getter->getValueByLanguagePriority($folder_data[$folder_id], 'title'),
		'folder_title'         => $getter->getValueByLanguagePriority($folder_data[$folder_id], 'title'),
		'folder_title_chinese' => $folder_data[$folder_id]['title_chinese'],
		'folder_title_english' => $folder_data[$folder_id]['title_english'],
		'folder_type'          => $getter->folderTypes[$folder_data[$folder_id]['folder_type_id']],
		'programme_id'         => $itemObj['programme_id'],
		'item_type'            => $itemType,
		'item_id'              => $item_id,
		'title'                => $getter->getValueByLanguagePriority($row, 'title'),
		'title_chinese'        => $row['title_chinese'],
		'title_english'        => $row['title_english'],
		'time'                 => $itemObj['modified_at'], // Get time of specific item
		'rank'                 => $rank,
		'description'          => $getter->getValueByLanguagePriority($row, 'description'),
		'description_chinese'  => $row['description_chinese'],
		'description_english'  => $row['description_english'],
	);

	return $folder_data;
}

/** 
 * Get Programme|Episode|Cast ids by folder id
 */
function PDESGetPECIdsByFolderId($folder_id){
	$dbh = DB3::get();

	$sql = 'select "p" as type,id from p_programme where status=1 and concat(",",folders,",") like "%,'.$folder_id.',%" union all ';
	$sql.= 'select "e" as type,id from p_episode where status=1 and concat(",",folders,",") like "%,'.$folder_id.',%" union all ';
	$sql.= 'select "c" as type,id from p_cast where status=1 and concat(",",folders,",") like "%,'.$folder_id.',%"';

	$ps  = $dbh->prepare($sql);
	$ret = $ps->execute();

	if(!$ret) {
		error_log("File '" . __File__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(),1));
		return false;
	}

	return $ps->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * for rerun-programme
 */
function PDESGetClonedFrom($programme_id){
	$dbh = DB3::get();
	$sql = 'select parent_id from p_programme_clone where programme_id=?';
	$ps  = $dbh->prepare($sql);

	$cloned_form = array();
	do{
		$ret = $ps->execute(array($programme_id));
		$row = $ps->fetch();
		if($row){
			$programme_id  = $row['parent_id'];
			array_unshift($cloned_form,$programme_id);
		}
	}while($row);

	return implode('->',$cloned_form);
}
